import getopt
import os
import shutil
import subprocess
import sys
import time
import traceback

from sqlalchemy import *
from sqlalchemy.orm import mapper
from sqlalchemy.orm import sessionmaker

from Repository import *
from Util import *

class DataSchema:
    
    def __init__(self):
        self.metadata = MetaData()

        #component examples: nepomuk-scribo, libscribo0, etc.
        self.component_table = Table('d4_component', self.metadata,
            Column('id_component', Integer, primary_key=True),
            Column('name', String(255), nullable=False),
            Column('summary', UnicodeText(), nullable=True),
            Column('description', UnicodeText(), nullable=True),
            Column('url', String(255), nullable=True),
            Column('category', String(255), nullable=True),
            Column('top_category', String(255), nullable=True),
            Column('maintainer', String(255), nullable=True),
            Column('license', String(255), nullable = True)
        )

        self.archive_table = Table('d4_archive', self.metadata,
            Column('id_archive', Integer, primary_key=True),
            Column('id_snapshot', Integer),
            Column('id_component', Integer),
            Column('file', String(255), nullable=True),
            Column('name', String(255), nullable=True),
            Column('version', String(255), nullable=True),
            Column('releas', String(255), nullable=True),
            Column('changelog', UnicodeText(), nullable=True),
            Column('build_host', String(255), nullable=True),
            Column('build_time', String(255), nullable=True),
            Column('sha1', String(255), nullable = True),
            Column('md5sum', String(255), nullable = True),
            Column('size', Integer, nullable = True),
            Column('installed_size', Integer, nullable = True),
            Column('prein', UnicodeText(), nullable = True),
            Column('postin', UnicodeText(), nullable = True),
            Column('preun', UnicodeText(), nullable = True),
            Column('postun', UnicodeText(), nullable = True),
            Column('from_archive', String(255), nullable = True),
            Column('is_source', Boolean(), nullable = True),
            Column('license', String(255), nullable = True),
            Column('creator', String(255), nullable = True),
            Column('file_count', Integer, nullable = True),
            Column('sloc_count', Integer, nullable = True)
        )

        
        self.file_table = Table('d4_file', self.metadata,
            Column('id_file', Integer, primary_key=True),
            Column('id_snapshot', Integer, nullable=False),
            Column('id_archive', Integer, nullable=False),
            Column('path', String(4000), nullable=False),
            Column('name', String(255), nullable=False),
            Column('type', String(255), nullable=True),
            Column('cat', String(255), nullable=True),
            Column('size', Integer, nullable=True),
            Column('extra', Boolean, nullable=True),
            Column('language', String(255), nullable=True),
            Column('sloc_count', String(255), nullable=True),
        )
    
        self.constraint_table = Table('d4_constraint', self.metadata,
            Column('id_constraint', Integer, primary_key=True),
            Column('id_snapshot', Integer, nullable=False),
            Column('id_archive', Integer, nullable=False),
            Column('type', String(255), nullable=False),
            Column('object', String(255), nullable=True),
            Column('cstraint', String(255), nullable=True),
        )

        self.snapshot_table = Table('d4_snapshot', self.metadata,
            Column('id_snapshot', Integer, primary_key=True),
            Column('id_repository', Integer, nullable=True),
            Column('snapshot_time', DateTime, nullable=True),
            Column('is_last', Boolean, nullable=True),
            Column('sloc_count', Integer, nullable=True),
            Column('archive_count', Integer, nullable=True),
            Column('file_count', Integer, nullable=True),
        )
    
    
    
        self.repository_table = Table('d4_repository', self.metadata,
            Column('id_repository', Integer, primary_key=True),
            Column('provider', String(255), nullable=True),
            Column('distribution', String(255), nullable=False),
            Column('url', String(255), nullable=True),
            Column('port', Integer, nullable=True),
            Column('login', String(255), nullable=True),
            Column('password', String(255), nullable=True),
            Column('architecture', String(255), nullable=True),
            Column('branch', String(255), nullable = True),
            Column('section', String(255), nullable = True)
        )
    
        self.distribution_table = Table('d4_distribution', self.metadata,
            Column('id_distribution', Integer, primary_key=True),
            Column('id_repository', Integer, primary_key=False),
            Column('name', String(255), nullable=False),
        )
    
        self.person_table = Table('d4_person', self.metadata,
            Column('id', Integer, primary_key=True),
            Column('name', String(255), nullable=False),
            Column('pseudo', String(255), nullable=True),
            Column('email', String(255), nullable=True),
        )
    
        self.queue_table = Table('d4_queue', self.metadata,
            Column('id_snapshot', Integer, nullable=False),
            Column('file', String(255), nullable=False),
            Column('action', String(255), nullable=True),
            Column('action_state', String(255), nullable=True),
            Column('error_message', String(255), nullable=True),
            Column('start_time', DateTime(), nullable=True),
            Column('end_time', DateTime(), nullable=True)
        )
        
        

    def create(self):
        dbname = "d4"
        engine = create_engine('mysql://root:@localhost/')
        try:
            engine.text("drop database %s " % dbname).execute()
        except Exception, e:
            print "Error: %s " % str(e)
        
        engine.text("create database %s " % dbname).execute()
        
        self.metadata.bind = self.get_engine()
        self.metadata.create_all()

        print "The data schema has been created successfully."

    
    def create_indexes(self):
        self.metadata.bind = self.get_engine()
        
#        i = Index('top_category', self.component_table.c.top_category)
#        i.create()
#        
#        i = Index('file', self.archive_table.c.file)
#        i.create()
#        
#        i = Index('file', self.queue_table.c.file)
#        i.create()
        
        i = Index('path', self.file_table.c.path)
        i.create()
        
        print "The indexes have been created successfully."
        
    def get_engine(self):
        return create_engine('mysql://root:@localhost/d4')
        
    def populate(self):
        mapper(Repository, self.repository_table)
        data = read_as_string('/home/arkub/Eclipse/Workspace-1/doc4packages/trunk/repositories.csv')
        lines = data.split('\n')
        
        Session = sessionmaker(bind=engine)
        session = Session()
        for line in lines:
            attributes = line.split(',')
            if len(attributes) > 1:
                repository = Repository(attributes[0], attributes[1], attributes[2], attributes[3], attributes[4], attributes[5], attributes[6], attributes[7], attributes[8])
                session.add(repository)
        session.commit()
        session.close()
        print 'The repositories have been loaded successfully.'

def main():

    schema = DataSchema()
    schema.create()
    schema.create_indexes()    
    schema.populate()


        
if __name__ == "__main__":
    main()

#connection = engine.connect()
#result = connection.execute("select * from REPOSITORIES")
#for row in result:
#    print "username:", row['id']
#connection.close()
